import logging
import math
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

my_pland_router = APIRouter(prefix="/school/CoursePlanD", tags=["班级课程计划管理"],
                            dependencies=[Depends(oauth2_scheme)])


class ClassCoursePlanSearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    fclassno: Optional[str] = Field("0", description="班级编号，默认0")
    fyear: Optional[str] = Field("", description="计划年份")
    fnote: Optional[str] = Field("", description="计划备注(支持前缀匹配)")


@my_pland_router.get("/list", summary="")
def list_my_pland(search: ClassCoursePlanSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from a_ts_courseplanh a,a_ts_coursepland b "
    sql = sql + ",a_ts_class c,a_ts_course d,a_ts_teacher e"
    sql = sql + " where a.fbillno=b.fbillno"
    sql = sql + " and b.fclass_no=c.fclass_no"
    sql = sql + " and b.fcourse_no=d.fcourse_no"
    sql = sql + " and b.fteacher_no=e.fteacher_no"
    if search.fsiteNo != "":
        sql = sql + " and a.fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fyear != "":
        sql = sql + " and a.fyear = :fyear "
        param["fyear"] = search.fyear
    if search.fclassno != "":
        sql = sql + " and b.fclass_no = :fclassno "
        param["fclassno"] = search.fclassno
    if search.fnote != "":
        sql = sql + " and a.fnote like concat('%', :fnote ,'%')"
        param["fnote"] = search.fnote
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select b.fbillno,b.fsite_no,b.fclass_no,b.fweek,b.ftime,b.fcourse_no,"
    sql = sql + " b.fteacher_no,b.fbegtime,b.fendtime,b.fnote,"
    sql = sql + " b.fstate,b.fupddate,b.foperator,"
    sql = sql + " c.fclass_name,d.fcourse_name,e.fteacher_name"
    sql = sql + " from a_ts_courseplanh a,a_ts_coursepland b "
    sql = sql + ",a_ts_class c,a_ts_course d,a_ts_teacher e"
    sql = sql + " where a.fbillno=b.fbillno"
    sql = sql + " and b.fclass_no=c.fclass_no"
    sql = sql + " and b.fcourse_no=d.fcourse_no"
    sql = sql + " and b.fteacher_no=e.fteacher_no"
    if search.fsiteNo != "":
        sql = sql + " and a.fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fyear != "":
        sql = sql + " and a.fyear = :fyear "
        param["fyear"] = search.fyear
    if search.fclassno != "":
        sql = sql + " and b.fclass_no = :fclassno "
        param["fclassno"] = search.fclassno
    if search.fnote != "":
        sql = sql + " and a.fnote like concat('%', :fnote ,'%')"
        param["fnote"] = search.fnote
    if search.order == "desc":
        sql = sql + " order by  b.fbillno desc "
    else:
        sql = sql + " order by  b.fbillno "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = search.limit
    param["offset"] = search.offset
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['fsiteno'] = data[key][1]
        d1['fclassno'] = data[key][2]
        d1['fweek'] = data[key][3]
        d1['ftime'] = data[key][4]
        d1['fcourseno'] = data[key][5]
        d1['fteacherno'] = data[key][6]
        d1['fbegtime'] = data[key][7]
        d1['fendtime'] = data[key][8]
        d1['fnote'] = data[key][9]
        d1['fstate'] = data[key][10]
        d1['fupddate'] = data[key][11]
        d1['foperator'] = data[key][12]
        d1['fclassname'] = data[key][13]
        d1['fcoursename'] = data[key][14]
        d1['fteachername'] = data[key][15]
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)
